package com.jyxy.service.web;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jyxy.service.model.CCompanyPatent;
import com.jyxy.service.util.StringUtil;

public class PlantService extends com.jyxy.service.web.base.BaseController {
	public void recommendList() {
		String areaName = getPara("area");
		if (StringUtil.isNotNullStr(areaName)) {
			String regStr = null;
			StringBuffer reg = new StringBuffer();
			if (areaName.indexOf(",") != -1) {
				String[] areas = areaName.split(",");
				String[] arrayOfString1;
				int j = (arrayOfString1 = areas).length;
				for (int i = 0; i < j; i++) {
					String area = arrayOfString1[i];
					reg.append(".*").append(area).append(".*|");
				}
				regStr = reg.substring(0, reg.length() - 1);
			} else {
				regStr = ".*" + areaName + ".*";
			}
			List<Record> lists = Db.use("plant").find("SELECT\n" + "	b.`name`,\n" + "	a.`drug_no`,\n"
					+ "	d.price,\n" + "	a.age_limit value,\n" + "	a.recommend\n" + "FROM\n" + "	`drug_area` a\n"
					+ "LEFT JOIN drug b ON a.drug_no = b.`no`\n"
					+ "LEFT JOIN drug_sell_price d ON d.drug_no = a.drug_no AND d.area_no=a.area_no,sys_area e\n"
					+ "WHERE a.area_no=e.`no` AND e.`name` REGEXP ?", regStr);
			rendSuccess(lists);
		} else {
			rendError(null, "查询区域不能为空");
		}
	}

	public void countItemList() {
		String areaName = getPara("drugNo");
		if (StringUtil.isNotNullStr(areaName)) {
			List<Record> lists = Db.use("plant").find("SELECT * FROM drug_cost_item WHERE drug_no=? order by `order`",
					new Object[] { areaName });
			rendSuccess(lists);
		} else {
			rendError(null, "药材编号不能为空");
		}
	}

	public void drugList() {
		String key = getPara("key");
		if (StringUtil.isNotNullStr(key)) {
			List<Record> lists = Db.use("plant").find(
					"SELECT `no`,`name` FROM drug a WHERE EXISTS (SELECT 1 FROM drug_cost_item WHERE drug_no=a.`no`) and `name` LIKE CONCAT('%',?,'%')",
					new Object[] { key });
			rendSuccess(lists);
		} else {
			List<Record> lists = Db.use("plant").find(
					"SELECT `no`,`name` FROM drug a WHERE EXISTS (SELECT 1 FROM drug_cost_item WHERE drug_no=a.`no`)");
			rendSuccess(lists);
		}
	}
	public void priceList() {
		int pageSize = this.getParaToInt("page_size", 10);
		Map<String, Object> reMap = new HashMap<>();
		int pageNum = this.getParaToInt("page_num", 1);
		String sortField=this.getPara("sort_field","create_date");
		String sortMethod=this.getPara("sort_method","");
		String name=this.getPara("name", "");
		String nameField=name.matches("[a-zA-Z]+")?"`spell`":"`name`";
		Record res=Db.findFirst("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='price_market' AND COLUMN_NAME=?",sortField);
		if(res==null){
			rendError(reMap,"字段不存在");
			return;
		}
		Page<Record> page = Db.paginate(pageNum, pageSize, "SELECT id,name,spec as specification,price,'中药材' classification,address as 'province',create_date as 'release_date',if(source=0,'中药材天地网','康美网') data_sources",
				"FROM plant.price_market where "+nameField+" like ? "
						+ "ORDER BY "+sortField+" "+sortMethod,"%"+name+"%");
		reMap.put("total", page.getTotalRow());
		reMap.put("data", page.getList());
		rendSuccess(reMap);
	}

	public static void main(String[] args) {
		System.out.println(
				"SELECT\r\n\tb.`name`,\r\n\ta.`drug_no`,\r\n\td.price,\r\n\tc.`value`,\r\n\ta.recommend\r\nFROM\r\n\t`drug_area` a\r\nLEFT JOIN drug b ON a.drug_no = b.`no`\r\nLEFT JOIN drug_cost_item c ON c.drug_no = a.drug_no AND c.lable='种植年限'\r\nLEFT JOIN drug_sell_price d ON a.drug_no = c.drug_no,sys_area e\r\nWHERE a.area_no=e.`no` AND e.`name` REGEXP ?");
	}
}
